﻿using System;
using System.Collections.Generic;
using System.Numerics;
using System.Data;
using System.Data.SqlClient;
using System.Text;


namespace WebShopData
{
    public class Article
    {
        public Article(string articleName, long unitPrice, string imageUrl, int categoryId, string status = "Active", int articleId = 0)
        {
            ArticleId = articleId;
            ArticleName = articleName;
            UnitPrice = unitPrice;
            ImageUrl = imageUrl;
            CategoryId = categoryId;
            Status = status;
        }

        public int ArticleId { get; set; }
        public string ArticleName { get; set; }
        public long UnitPrice { get; set; }
        public string ImageUrl { get; set; }
        public int CategoryId { get; set; }
        public string Status { get; set; }

        public override string ToString()
        {
            return string.Format("{0} {1} {2} {3}", ArticleId, ArticleName, UnitPrice, CategoryId);
        }
    }

    public static class ArticleDB
    {
        public static int InsertNewArticle(Article article)
        {
            using (SqlConnection connection = GalaxySale2014DB.GetConnection())
            {
                using (var command = connection.CreateCommand())
                {
                    StringBuilder sqlQuery = new StringBuilder("INSERT INTO [dbo].[Articles]([ArticleName],[UnitPrice],[ImageUrl],[CategoryId],[Status])");
                    sqlQuery.Append(" VALUES(@ArticleName, @UnitPrice, @ImageUrl, @CategoryId, @Status)");
                    command.CommandText = sqlQuery.ToString();

                    command.Parameters.Add("ArticleName", SqlDbType.VarChar, 100).Value = article.ArticleName;
                    command.Parameters.Add("UnitPrice", SqlDbType.BigInt).Value = article.UnitPrice;
                    command.Parameters.Add("ImageUrl", SqlDbType.VarChar, 255).Value = article.ImageUrl;
                    command.Parameters.Add("CategoryId", SqlDbType.Int).Value = article.CategoryId;
                    command.Parameters.Add("Status", SqlDbType.VarChar, 50).Value = article.Status;

                    int affectedRows = command.ExecuteNonQuery();
                    return affectedRows;
                }
            }
        }

        public static List<Article> GetAllArticles()
        {
            return GetArticlesBase("", new List<SqlParameter>());
        }

        public static Article GetArticleById(int articleId)
        {
            string whereLine = " WHERE [ArticleId] = @ArticleId";
            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlParameter sqlParameter = new SqlParameter("ArticleId", SqlDbType.Int);
            sqlParameter.Value = articleId;
            parameters.Add(sqlParameter);
            List<Article> articles = GetArticlesBase(whereLine, parameters);
            if (articles.Count == 1)
            {
                return articles[0];
            }
            return null;
        }

        public static List<Article> GetArticlesBase(string whereLine, List<SqlParameter> parameters)
        {
            DataSet dataSetArticles = GetArticlesAsDataSet(whereLine, parameters);
            List<Article> articles = new List<Article>();
            foreach (DataRow row in dataSetArticles.Tables[0].Rows)
            {
                articles.Add(new Article(
                    articleName: row["ArticleName"].ToString().Trim(),
                    unitPrice: long.Parse(row["UnitPrice"].ToString().Trim()),
                    imageUrl: row["ImageUrl"].ToString().Trim(),
                    categoryId: int.Parse(row["CategoryId"].ToString().Trim()),
                    status: row["Status"].ToString().Trim(),
                    articleId: int.Parse(row["ArticleId"].ToString().Trim())));
            }
            return articles;
        }

        public static DataSet GetArticlesAsDataSet(string whereLine, List<SqlParameter> parameters)
        {
            using (var connection = GalaxySale2014DB.GetConnection())
            {
                using (var command = connection.CreateCommand())
                {
                    StringBuilder sqlQuery = new StringBuilder("SELECT [ArticleId],[ArticleName],[UnitPrice],[ImageUrl],[CategoryId],[Status]");
                    sqlQuery.Append(" FROM [dbo].[ArticlesNotDeletedView]");
                    sqlQuery.Append(whereLine);
                    command.CommandText = sqlQuery.ToString();
                    foreach (SqlParameter sqlParameter in parameters)
                    {
                        command.Parameters.Add(sqlParameter);
                    }
                    DataSet result = GalaxySale2014DB.GetDataSet(command);
                    return result;
                }
            }
        }

        public static int UpdateArticle(Article article)
        {
            using (var connection = GalaxySale2014DB.GetConnection())
            {
                using (var command = connection.CreateCommand())
                {
                    StringBuilder sqlQuery = new StringBuilder("UPDATE [dbo].[Articles]");
                    sqlQuery.Append(" SET [ArticleName] = @ArticleName");
                    sqlQuery.Append(", [UnitPrice] = @UnitPrice");
                    sqlQuery.Append(", [ImageUrl] = @ImageUrl");
                    sqlQuery.Append(", [CategoryId] = @CategoryId");
                    sqlQuery.Append(", [Status] = @Status");
                    sqlQuery.Append(" WHERE [ArticleId] = @ArticleId");
                    command.CommandText = sqlQuery.ToString();

                    command.Parameters.Add("ArticleName", SqlDbType.VarChar, 100).Value = article.ArticleName;
                    command.Parameters.Add("UnitPrice", SqlDbType.BigInt).Value = article.UnitPrice;
                    command.Parameters.Add("ImageUrl", SqlDbType.VarChar, 255).Value = article.ImageUrl;
                    command.Parameters.Add("CategoryId", SqlDbType.Int).Value = article.CategoryId;
                    command.Parameters.Add("Status", SqlDbType.VarChar, 50).Value = article.Status;
                    command.Parameters.Add("ArticleId", SqlDbType.Int).Value = article.ArticleId;

                    int affectedRows = command.ExecuteNonQuery();
                    return affectedRows;
                }
            }
        }

        public static int DeleteArticleById(int articleId)
        {
            Article article = GetArticleById(articleId);
            article.Status = "Deleted";
            return UpdateArticle(article);
            //using (var connection = GalaxySale2014DB.GetConnection())
            //{
            //    using (var command = connection.CreateCommand())
            //    {
            //        StringBuilder sqlQuery = new StringBuilder("DELETE FROM [dbo].[Articles]");
            //        sqlQuery.Append("WHERE [ArticleId] = @ArticleId");
            //        command.CommandText = sqlQuery.ToString();

            //        command.Parameters.Add("ArticleId", SqlDbType.Int).Value = articleId;

            //        int affectedRows = command.ExecuteNonQuery();
            //        return affectedRows;
            //    }
            //}
        }
    }
}